BigQuery＆Lookerでのクエリデータ量の変化を調べてみた #BigQuery #Looker

「カスタマーストーリー」を支えるツール特集

#ビジネス

#Looker

#Google BigQuery

#Google Cloud (GCP)

さがら

2020.12.02

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

※本エントリは、クラスメソッド BigQuery Advent Calendar 2020の2日目のエントリです。私にとっては、BigQueryが関わる初ブログとなります！

突然ですが、Lookerを使用する方はDWHにBigQueryを選定して使用されている方が多い印象を私は受けています。

一方、BigQueryが他のクラウド型DWHと大きく違うところは「クエリしたデータ量により、課金される」点かと思います。

このため、「Lookerをユーザーにどんどん使わせて良いのかな…データ量大丈夫かな…」と不安になる方もいると思います。

そこで、どういったときにどんなクエリを発行しているのか、自分が気になっていた範囲で簡単に調査を行ってみたため、本ブログにてまとめたいと思います。

調査の観点

以下の観点から、クエリの対象となるデータ量の調査を行います。

グラフ作成
- 昇順、降順入れ替えた時
- グラフの種類を変更した時
- ROW LIMITを変えた時
JOIN＆フィルタ関係
- JOINして、フィルタかけない時
- JOINして、Explore上でpartitionしているフィールドでフィルタかけた時
- JOINして、LookMLのsql_whereを使ってpartitionしているフィールドでフィルタかけた時

使用するデータ

KaggleのRecruit Restaurant Visitor Forecastingのデータを使用します。

このコンペはリクルート社が主催であり、ホットペッパーグルメやAirレジのデータを用いて、レストランごとにいつどれだけの集客がありそうかを予測する、というコンペです。

…とはいってもコンペの取り組みをこのブログ紹介するわけではありません。笑

今回このデータを用いた理由は、日付でのパーティションがしやすく、特にデータの加工をせずとも検証環境のコストを圧迫しないデータ量だからです。

BigQueryのパーティショニングに関しては、公式ドキュメントを参考にしてみてください。

今回の検証では、「日別パーティション分割テーブル」を使用します。

実際に検証で使用するデータは以下の内容です。どちらも日別に集計しているため、この２つのテーブルのリレーションは「one_to_one」になります。

テーブル名：air_reserve_bydate_partition
- データの変更箇所：元のair_reserve.csvでは、visit_datetimeというフィールドが時間と分まで含めたデータであったため、日別に再集計(visit_dateを新しく定義)した
- レコード数：29830行
- SELECT *実行時の処理データ量：1.1MB
  - SELECT air_store_id実行時の処理データ量：640.9KB
  - SELECT visit_date実行時の処理データ量：233KB
  - SELECT reserve_visitors実行時の処理データ量：233KB
- パーティション：visit_datetimeを日単位でまとめた「visit_date」を使用。
テーブル名：air_visit_data_partition
- データの変更箇所：特になし、元のair_visit_data.csvをそのまま使用。
- レコード数：252108行
- SELECT * 実行時の処理データ量：9.1MB
  - SELECT air_store_id実行時の処理データ量：5.3MB
  - SELECT visit_date実行時の処理データ量：1.9MB
  - SELECT visitors実行時の処理データ量：1.9MB
- パーティション：「visit_date」を使用。

調査結果

グラフ作成（昇順、降順、入れ替えた時）

手順

air_reserve_bydate内の「Air_store_ID」「count」を選択
Clear Cache & Refreshを押して実行
その後、Data欄のCountを押し、昇順に変更

結果

下図のように、最初に実行したときのクエリだけがBigQuery上の履歴に残っていました！

降順と昇順の切り替えはキャッシュで上手く出来るようです。

このため、昇順と降順をひたすらに切り替えまくってもOKですね。

グラフ作成（グラフの種類を変更した時）

手順

air_reserve_bydate内の「Air_store_ID」「count」を選択
Clear Cache & Refreshを押して実行
VISUALIZATIONを押して、Column、Barと続けてクリックして変更してみる

結果

こちらも下図のように、最初に実行したクエリだけが履歴に残っていました。

これは「当たり前じゃん」と思うかもしれない調査でしたが、実際にやってみて確信に変わりましたね！

グラフ作成（ROW LIMITを変えた時）

手順

air_reserve_bydate_partition内の「Air_store_ID」「count」を選択
Clear Cache & Refreshを押して実行
ROW LIMITを500→1000に変更
ROW LIMITを1000→100に変更
ROW LIMITを100→500に変更

結果

こちらは、合計3回クエリの履歴が残っていることがわかりました。

下図で詳細を表示しているのが最初にROW LIMIT500で実行した時で、その上にROW LIMITを1000に変更したとき、100に変更した時、2回分の履歴が残っています。

一方で、最後に500を再度実行した際はクエリが発行されずキャッシュが使用されているようです。

そのため、ROW LIMITの変更を違う値で繰り返し行うことは避けたほうが良さそうですね！

上述の降順・昇順の切り替えではクエリを新しく発行せずにキャッシュだけで出来ていたので、ROW LIMITの数値を変えた場合でもキャッシュを使用できるのかと思っていたのですが…

これはやってみた価値がありました！

JOINして、フィルタかけない時

手順

下図のように、JOINしたExploreを作成
Explore上で何もフィルタをかけずに、air_reserve_bydate_partition内の「visit_date」、air_visit_data_partition内の「total_visitors」を選択してRUNを実行。

結果

データ処理量は9.99MBとなりました。だいたい、air_reserve_bydate_partitionとair_visit_data_partition、それぞれのSELECT*実行時のデータ処理量を足し合わせたくらいのデータ処理量になりましたね。選択している項目は２つだけなのですが、JOINしたデータどちらもほぼ全て処理している、というのが意外な印象でした。

JOINして、Explore上でpartitionしているフィールドでフィルタかけた時

手順

下図のように、JOINしたExploreを作成（※1つ前のJOINの例と同じものです）
パーティションを設定しているair_visit_data_partition内の「visit_date」を2016年11月だけでフィルタした上で、air_reserve_bydate_partition内の「visit_date」、air_visit_data_partition内の「total_visitors」を選択してRUNを実行。（2016年11月だけでフィルタすると、air_visit_data_partition内の全体の10分の1ほどのデータ量です。）

結果

データ処理量は、1.59MBとなりました。

かなり削減されましたね！

BigQueryに発行されたクエリは、以下の内容です。

SELECT 
	CAST(CAST(air_reserve_bydate_partition.visit_date  AS TIMESTAMP) AS DATE) AS air_reserve_bydate_partition_visit_date,
	COALESCE(SUM(air_visit_data_partition.visitors ), 0) AS air_visit_data_partition_total_visitors
FROM `kaggle_recruit_data_partition.air_reserve_bydate_partition`
     AS air_reserve_bydate_partition
LEFT JOIN `kaggle_recruit_data_partition.air_visit_data_partition`
     AS air_visit_data_partition ON air_reserve_bydate_partition.air_store_id = air_visit_data_partition.air_store_id
            AND (CAST(CAST(air_reserve_bydate_partition.visit_date  AS TIMESTAMP) AS DATE)) = (CAST(CAST(air_visit_data_partition.visit_date  AS TIMESTAMP) AS DATE))

WHERE 
	(((air_visit_data_partition.visit_date ) >= (DATE(TIMESTAMP_TRUNC(CAST(TIMESTAMP('2016-11-01 00:00:00') AS TIMESTAMP), DAY))) AND (air_visit_data_partition.visit_date ) < (DATE(TIMESTAMP_TRUNC(CAST(TIMESTAMP('2016-11-30 00:00:00') AS TIMESTAMP), DAY)))))
GROUP BY 1
ORDER BY 1 DESC
LIMIT 500

やはり、BigQueryにおけるパーティションの設定と、そのフィールドを用いたフィルタの設定はとても重要だということが明確になりましたね！

Explore上でパーティションしているフィールドでのフィルタ設定を必須にしたいならば、always_filterやconditionally_filterを使用しましょう！

JOINして、LookMLのsql_whereを使ってpartitionしているフィールドでフィルタかけた時

手順

先程の「JOINして、Explore上でpartitionしているフィールドでフィルタかけた時」と同じことを、LookMLでexploreを定義する際に使用できるsql_whereを用いて実現したときに、違いがあるかを検討します。

下図のように、JOINしたExploreを作成。ただし、これまでの例と違って、joinパラメータ内にsql_whereを追加して、2016年11月だけでフィルタをかけるように設定しています。
Explore上で何もフィルタをかけずに、air_reserve_bydate_partition内の「visit_date」、air_visit_data_partition内の「total_visitors」を選択してRUNを実行。

結果

データ処理量は、9.99MBとなりました。

なんと、フィルタをかけていないときと全く同じ結果になりました。

BigQueryに発行されたクエリは、以下の内容です。

SELECT 
	CAST(CAST(air_reserve_bydate_partition.visit_date  AS TIMESTAMP) AS DATE) AS air_reserve_bydate_partition_visit_date,
	COALESCE(SUM(air_visit_data_partition.visitors ), 0) AS air_visit_data_partition_total_visitors
FROM `kaggle_recruit_data_partition.air_reserve_bydate_partition`
     AS air_reserve_bydate_partition
LEFT JOIN `kaggle_recruit_data_partition.air_visit_data_partition`
     AS air_visit_data_partition ON air_reserve_bydate_partition.air_store_id = air_visit_data_partition.air_store_id
            AND (CAST(CAST(air_reserve_bydate_partition.visit_date  AS TIMESTAMP) AS DATE)) = (CAST(CAST(air_visit_data_partition.visit_date  AS TIMESTAMP) AS DATE))

WHERE (CAST(CAST(air_reserve_bydate_partition.visit_date  AS TIMESTAMP) AS DATE)) >= DATE('2016-11-01')
              AND (CAST(CAST(air_reserve_bydate_partition.visit_date  AS TIMESTAMP) AS DATE)) <DATE('2016-11-30') 
GROUP BY 1
ORDER BY 1 DESC
LIMIT 500

クエリの内容を比較してみると、先程のExplore上でフィルタをかけた場合との違いは、「air_reserve_bydate_partitionのvisit_dateをWHERE句内でCASTしているか、していないか」という点であることがわかりました。

ただ下図の通り、air_reserve_bydate_partitionのvisit_dateは元々DATE型ですし、このCASTのところも見ると、TIMESTAMP型にCASTしてからDATE型にCASTするという、よくわからない挙動をしていることがわかります…

ちょっと、私に調べられるのはここが限界でした…（すみません…）

ひとまずここで言えることは、パーティションしているフィールドでsql_whereするときには、今回の例のように適切にパーティションが効かない場合があることだけ、注意してください！

個人的には、sql_where使うケースは稀かと思いますので、基本的にはExplore上でフィルタすることがおすすめです！

まとめ

今回の検討を通して、判明したことを以下にまとめます。

グラフ作成において
- 昇順降順の切り替え、グラフの種類を切り替えたときはキャッシュを活用している
- ROW LIMITを違う値に変更すると、その値でのキャッシュがない場合、都度新しいクエリが発行される
JOIN＆フィルタにおいて
- BigQueryでパーティションを設定したフィールドをExplore内のフィルタで絞り込みを行うと、データ処理量もフィルタ内容に応じて削減される
- 一方で、LookMLでexploreパラメータ内でsql_whereを用いてパーティションを設定したフィールドをフィルタした際は、データ処理量がフィルタ内容に応じて削減されないことがある